package ffmvc.model;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import com.google.gson.Gson;
import ffmvc.utils.Consts;

public class JdbcDataAccess implements IDao {

	private Gson gson;

	private JdbcTemplate select;

	private Map<String, Manager> managers = null;

	private Map<String, Player> players = null;

	private Map<String, Team> teams = null;

	public JdbcDataAccess() {
	}

	public void setGson(Gson gson) {
		this.gson = gson;
	}

	public void setSelect(JdbcTemplate select) {
		this.select = select;
	}

	private Manager getManagerById(String id) {
		Map<String, Manager> managers = getManagers();
		return managers.get(id);

	}

	public Player getPlayerById(String id) {
		Map<String, Player> players = getPlayers();
		return players.get(id);
	}

	public Team getTeamById(String id) {
		Map<String, Team> teams = getTeams();
		Team team = teams.get(id);
		return team;
	}

	private List<Manager> getAllManagers() {

		List<Manager> temp = select.query("select ID, TEAM_NAME from manager", new ManagerRowMapper());
		return temp;

	}

	private List<Player> getAllPlayers() {

		List<Player> temp = select.query("select NAME, PLAYER_ID, TEAM_ID, POSITION from player", new PlayerRowMapper());
		return temp;
	}

	private List<Team> getAllTeams() {
		List<Team> temp = select.query("select team_id, name from team", new TeamRowMapper());
		return temp;
	}

	public String getAllPerformances() {

		List<Performance> temp = select
				.query("select id, player_id, season, points, home_team_id, week, away_team_id, assists, goals_allowed, fouls_won, yellow_cards, penalty_kick_goals, fouls_committed, match_winning_goal, blocked_shots, penalty_kick_saves, tackles_won, give, penalties_committed, shots_on_target, red_card, passes_intercepted, penalty_kick_misses, goals, games_played, defeat, tie, win, clean_sheet, own_goals, set_piece_goals, saves, successful_crosses, salary, owned_starmo, owned from performance",
						new PerformanceRowMapper());
		String jsonTemp = gson.toJson(temp);
		return jsonTemp;
	}

	public List<Performance> getAllPerformancesByPlayer(String playerId) {

		List<Performance> temp = select
				.query("select id, player_id, season, points, home_team_id, week, away_team_id, assists, goals_allowed, fouls_won, yellow_cards, penalty_kick_goals, fouls_committed, match_winning_goal, blocked_shots, penalty_kick_saves, tackles_won, give, penalties_committed, shots_on_target, red_card, passes_intercepted, penalty_kick_misses, goals, games_played, defeat, tie, win, clean_sheet, own_goals, set_piece_goals, saves, successful_crosses, salary, owned_starmo, owned from performance where player_id = ?",
						new Object[] { playerId }, new PerformanceRowMapper());
		for (Performance perf : temp) {
			fillPerformance(perf);
		}

		return temp;
	}

	public List<Performance> getAllPerformancesByTeam(String teamId) {

		List<Performance> temp = select
				.query("select id, player_id, season, points, home_team_id, week, away_team_id, assists, goals_allowed, fouls_won, yellow_cards, penalty_kick_goals, fouls_committed, match_winning_goal, blocked_shots, penalty_kick_saves, tackles_won, give, penalties_committed, shots_on_target, red_card, passes_intercepted, penalty_kick_misses, goals, games_played, defeat, tie, win, clean_sheet, own_goals, set_piece_goals, saves, successful_crosses, salary, owned_starmo, owned from performance where home_team_id = ? or away_team_id=?",
						new Object[] { teamId, teamId }, new PerformanceRowMapper());
		return temp;
	}

	public String getAllPerformancesByTeamInWeek(String teamId, String week, String season) {

		List<Performance> temp = select
				.query("select id, player_id, season, points, home_team_id, week, away_team_id, assists, goals_allowed, fouls_won, yellow_cards, penalty_kick_goals, fouls_committed, match_winning_goal, blocked_shots, penalty_kick_saves, tackles_won, give, penalties_committed, shots_on_target, red_card, passes_intercepted, penalty_kick_misses, goals, games_played, defeat, tie, win, clean_sheet, own_goals, set_piece_goals, saves, successful_crosses, salary, owned_starmo, owned from performance where (home_team_id = ? or away_team_id=?) and week=? and season=?",
						new Object[] { teamId, teamId, week, season }, new PerformanceRowMapper());
		String jsonTemp = gson.toJson(temp);
		return jsonTemp;
	}

	public String getTopPerformancesInWeek(Integer week, String season, Integer count) {

		List<Performance> temp = select
				.query("select id, player_id, season, points, home_team_id, week, away_team_id, assists, goals_allowed, fouls_won, yellow_cards, penalty_kick_goals, fouls_committed, match_winning_goal, blocked_shots, penalty_kick_saves, tackles_won, give, penalties_committed, shots_on_target, red_card, passes_intercepted, penalty_kick_misses, goals, games_played, defeat, tie, win, clean_sheet, own_goals, set_piece_goals, saves, successful_crosses, salary, owned_starmo, owned from performance where week=? and season=? ORDER BY points desc",
						new Object[] { week, season }, new PerformanceRowMapper());
		List<Performance> top = new ArrayList<Performance>(temp.subList(0, count));
		String jsonTemp = gson.toJson(top);
		return jsonTemp;
	}

	public List<AggPerformance> getTopPerfomancesInPeriod(Integer startWeek, Integer endWeek, String season, Integer count) {

		List<Object> weeks = new ArrayList<Object>();
		for (Integer i = startWeek; i <= endWeek; i++) {
			weeks.add(i);
		}

		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("ids", weeks);
		parameters.addValue("season", season);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		String sql = "select  player_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance where week in (:ids) and season=:season GROUP BY player_id, season order by SUM(points)DESC";
		List<AggPerformance> temp = namedParameterJdbcTemplate.query(sql, parameters, new AggPerformanceRowMapper());

		List<AggPerformance> top = new ArrayList<AggPerformance>(temp.subList(0, count));
		for (int i = 0; i < top.size(); i++) {

			Player player = getPlayerById(top.get(i).getPlayerId());
			top.get(i).setName(player.getName());
		}
		return top;
	}

	public List<AggPerformance> getPlayerPerfomances(String playerId, String season) {

		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("season", season);
		parameters.addValue("playerId", playerId);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		String sql = "select  player_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance where player_id = :playerId and season=:season GROUP BY player_id, season order by SUM(points)DESC";
		List<AggPerformance> temp = namedParameterJdbcTemplate.query(sql, parameters, new AggPerformanceRowMapper());

		return temp;
	}

	public List<AggPerformance> getPlayerPerfomancesAway(String playerId, String season) {

		MapSqlParameterSource parameters = new MapSqlParameterSource();

		Player player = getPlayerById(playerId);
		String playersTeamId = player.getTeamId();
		parameters.addValue("season", season);
		parameters.addValue("playerId", playerId);
		parameters.addValue("teamId", playersTeamId);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		String sql = "select  player_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance where player_id = :playerId and season=:season and away_team_id =:teamId  GROUP BY player_id, season order by SUM(points)DESC";
		List<AggPerformance> temp = namedParameterJdbcTemplate.query(sql, parameters, new AggPerformanceRowMapper());
		for (AggPerformance perf : temp) {
			fillAggPerformance(perf);
		}

		return temp;
	}

	public List<AggPerformance> getPlayerPerfomancesHome(String playerId, String season) {

		MapSqlParameterSource parameters = new MapSqlParameterSource();

		Player player = getPlayerById(playerId);
		String playersTeamId = player.getTeamId();
		parameters.addValue("season", season);
		parameters.addValue("playerId", playerId);
		parameters.addValue("teamId", playersTeamId);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		String sql = "select  player_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance where player_id = :playerId and season=:season and home_team_id =:teamId GROUP BY player_id, season order by SUM(points)DESC";
		List<AggPerformance> temp = namedParameterJdbcTemplate.query(sql, parameters, new AggPerformanceRowMapper());
		for (AggPerformance perf : temp) {
			fillAggPerformance(perf);
		}

		return temp;
	}

	public List<AggManagerPerformance> getTopManagerPerformancesInPeriod(Integer startWeek, Integer endWeek, Integer season) {
		List<Object> weeks = new ArrayList<Object>();
		for (Integer i = startWeek; i <= endWeek; i++) {
			weeks.add(i);
		}

		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("ids", weeks);
		parameters.addValue("season", season);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		String sql = "select  manager_id, SUM(total_points) from manager_performance where week in (:ids) and season=:season GROUP BY  manager_id, season order by SUM(total_points)DESC";
		List<AggManagerPerformance> temp = namedParameterJdbcTemplate.query(sql, parameters, new AggManagerPerformanceRowMapper());

		for (int i = 0; i < temp.size(); i++) {

			Manager manager = getManagerById(temp.get(i).getManagerId());
			temp.get(i).setManagerName(manager.getTeamName());
		}

		return temp;
	}

	private Map<String, Manager> getManagers() {
		if (managers == null) {
			List<Manager> dbManagers = getAllManagers();
			managers = new HashMap<String, Manager>();
			for (Manager manager : dbManagers) {
				managers.put(manager.getId(), manager);
			}
		}
		return managers;
	}

	private Map<String, Player> getPlayers() {
		if (players == null) {
			List<Player> dbPlayers = getAllPlayers();
			players = new HashMap<String, Player>();
			for (Player player : dbPlayers) {
				players.put(player.getId(), player);
			}
		}
		return players;
	}

	private Map<String, Team> getTeams() {
		if (teams == null) {
			List<Team> dbTeams = getAllTeams();
			teams = new HashMap<String, Team>();
			for (Team team : dbTeams) {
				teams.put(team.getId().trim(), team);
			}
		}
		return teams;
	}

	private void fillPerformance(Performance perf) {
		// fill null values
		String playersId = perf.getPlayerId();
		Player player = getPlayerById(playersId);
		perf.setPlayer(player);
		perf.getFoulsWonCommitedRatio();
		perf.getAveragePoints();

	}

	private void fillAggPerformance(AggPerformance perf) {
		// fill null values
		String playersId = perf.getPlayerId();
		Player player = getPlayerById(playersId);
		perf.setPlayer(player);
		perf.getFoulsWonCommittedRatio();
		perf.getAveragePoints();
	}

	public List<AggPerformance> getTeamPerfomancesHome(String teamId, String season) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("season", season);
		parameters.addValue("teamId", teamId);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		String sql = "select  player_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance where player_id in (select player_id from player where team_id=:teamId) and home_team_id=:teamId and season=:season GROUP BY player_id, season order by SUM(points)DESC";
		List<AggPerformance> temp = namedParameterJdbcTemplate.query(sql, parameters, new AggPerformanceRowMapper());
		for (AggPerformance perf : temp) {
			fillAggPerformance(perf);
		}
		return temp;
	}

	public List<AggPerformance> getTeamPerfomancesAway(String teamId, String season) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("season", season);
		parameters.addValue("teamId", teamId);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		String sql = "select  player_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance where player_id in (select player_id from player where team_id=:teamId) and away_team_id=:teamId and season=:season GROUP BY player_id, season order by SUM(points)DESC";
		List<AggPerformance> temp = namedParameterJdbcTemplate.query(sql, parameters, new AggPerformanceRowMapper());
		for (AggPerformance perf : temp) {
			fillAggPerformance(perf);
		}
		return temp;
	}

	public List<AggPerformance> getTeamPerfomances(String teamId, String season) {

		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("season", season);
		parameters.addValue("teamId", teamId);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		String sql = "select  player_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance where player_id in (select player_id from player where team_id=:teamId) and season=:season GROUP BY player_id, season order by SUM(points)DESC";
		List<AggPerformance> temp = namedParameterJdbcTemplate.query(sql, parameters, new AggPerformanceRowMapper());
		for (AggPerformance perf : temp) {
			fillAggPerformance(perf);
		}

		return temp;
	}

	public PerformancesAll getTeamPerfomances(PerformancesAll perfs, String teamId, String season) {

		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("season", season);
		parameters.addValue("teamId", teamId);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		String sql = "select  player_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance where player_id in (select player_id from player where team_id=:teamId) and season=:season GROUP BY player_id, season order by SUM(points)DESC";
		List<AggPerformance> tempAll = namedParameterJdbcTemplate.query(sql, parameters, new AggPerformanceRowMapper());
		for (AggPerformance perf : tempAll) {
			fillAggPerformance(perf);
		}
		sql = "select  player_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance where player_id in (select player_id from player where team_id=:teamId) and away_team_id=:teamId and season=:season GROUP BY player_id, season order by SUM(points)DESC";
		List<AggPerformance> tempAway = namedParameterJdbcTemplate.query(sql, parameters, new AggPerformanceRowMapper());
		for (AggPerformance perf : tempAway) {
			fillAggPerformance(perf);
		}
		sql = "select  player_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance where player_id in (select player_id from player where team_id=:teamId) and home_team_id=:teamId and season=:season GROUP BY player_id, season order by SUM(points)DESC";
		List<AggPerformance> tempHome = namedParameterJdbcTemplate.query(sql, parameters, new AggPerformanceRowMapper());
		for (AggPerformance perf : tempHome) {
			fillAggPerformance(perf);
		}

		perfs.setOverall(tempAll);
		perfs.setAway(tempAway);
		perfs.setHome(tempHome);

		return perfs;
	}

	public TeamPerformancesAll getTeamsPerfomances(TeamPerformancesAll perfs, String season) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("season", season);

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		String sql = "select  team_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance join player on performance.player_id=player.player_id where season='2013' GROUP BY player.team_id, season order by SUM(points)DESC;";
		List<AggTeamPerformance> tempAll = namedParameterJdbcTemplate.query(sql, parameters, new AggTeamPerformanceRowMapper());
		for (AggTeamPerformance perf : tempAll) {
			fillAggTeamPerformance(perf);
		}
		sql = "select  team_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance join player on performance.player_id=player.player_id where season='2013' and performance.away_team_id=player.team_id GROUP BY player.team_id, season order by SUM(points)DESC;";
		List<AggTeamPerformance> tempAway = namedParameterJdbcTemplate.query(sql, parameters, new AggTeamPerformanceRowMapper());
		for (AggTeamPerformance perf : tempAway) {
			fillAggTeamPerformance(perf);
		}
		sql = "select  team_id, season, SUM(points), SUM(assists), SUM(goals_allowed), SUM(fouls_won), SUM(yellow_cards), SUM(penalty_kick_goals), SUM(fouls_committed), SUM(match_winning_goal), SUM(blocked_shots), SUM(penalty_kick_saves), SUM(tackles_won), SUM(give), SUM(penalties_committed), SUM(shots_on_target), SUM(red_card), SUM(passes_intercepted), SUM(penalty_kick_misses), SUM(goals), SUM(games_played), SUM(defeat), SUM(tie), SUM(win), SUM(clean_sheet), SUM(own_goals), SUM(set_piece_goals), SUM(saves), SUM(successful_crosses), SUM(salary), SUM(owned_starmo), SUM(owned) from performance join player on performance.player_id=player.player_id where season='2013' and performance.home_team_id=player.team_id GROUP BY player.team_id, season order by SUM(points)DESC;";
		List<AggTeamPerformance> tempHome = namedParameterJdbcTemplate.query(sql, parameters, new AggTeamPerformanceRowMapper());
		for (AggTeamPerformance perf : tempHome) {
			fillAggTeamPerformance(perf);
		}

		perfs.setOverall(tempAll);
		perfs.setAway(tempAway);
		perfs.setHome(tempHome);

		return perfs;
	}

	private void fillAggTeamPerformance(AggTeamPerformance perf) {
		String teamsId = perf.getTeamId();
		Team team = getTeamById(teamsId);
		perf.setTeam(team);
	}

	public void insertPlayers(List<JSONHolder> holders) {

		MapSqlParameterSource parameters = new MapSqlParameterSource();

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		for (JSONHolder holder : holders) {
			JSONObject playerJSON = holder.getPlayer();
			JSONObject playerHistoryJSON = holder.getPlayerHistory();
			JSONObject playerStatsJSON = holder.getPlayerStats();

			Player player = populatePlayer(playerJSON, playerHistoryJSON, playerStatsJSON);

			parameters.addValue("name", player.getName());
			parameters.addValue("teamId", player.getTeamId());
			parameters.addValue("id", player.getId());
			parameters.addValue("position", player.getPosition());
			parameters.addValue("lastSeasonPoints", player.getLastSeasonPoints());
			parameters.addValue("lastSeasonPlayed", player.getLastSeasonsGamesPlayed());
			String sql = "insert into player (player_id, name, team_id, position,last_season_points, last_season_games) values (:id, :name,:teamId,:position,:lastSeasonPoints,:lastSeasonPlayed)";
			namedParameterJdbcTemplate.update(sql, parameters);

		}
	}

	// JSON TO JAVA BRIDGE - DANGER DANGER
	private Player populatePlayer(JSONObject playerJSON, JSONObject playerHistoryJSON, JSONObject playerStatsJSON) {
		Player player = new Player();
		try {
			player.setPosition(playerJSON.getString("position"));
			player.setLastSeasonPoints(playerJSON.getInt("last_season_points"));
			player.setLastSeasonsGamesPlayed(playerJSON.getInt("last_season_GP"));
			player.setId(playerJSON.getString("id"));
			player.setName(playerJSON.getString("name"));
			player.setTeamId(playerJSON.getString("team_id"));
		} catch (JSONException e) {
			e.printStackTrace();
		}

		return player;
	}

	public void insertTeams(List<JSONHolder> holders) {

		MapSqlParameterSource parameters = new MapSqlParameterSource();
		Set<Team> teams = new HashSet<Team>();

		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		for (JSONHolder holder : holders) {
			JSONObject playerJSON = holder.getPlayer();
			JSONObject playerHistoryJSON = holder.getPlayerHistory();
			JSONObject playerStatsJSON = holder.getPlayerStats();

			Team team = populateTeam(playerJSON, playerHistoryJSON, playerStatsJSON);
			teams.add(team);

		}
		for (Team team : teams) {
			parameters.addValue("name", team.getTeamName());
			parameters.addValue("teamId", team.getId());
			String sql = "insert into team (name, team_id) values (:name,:teamId)";
			namedParameterJdbcTemplate.update(sql, parameters);
		}

	}

	private Team populateTeam(JSONObject playerJSON, JSONObject playerHistoryJSON, JSONObject playerStatsJSON) {
		Team team = new Team();

		try {
			team.setTeamName(playerJSON.getString("team_name").trim());
			team.setId(playerJSON.getString("team_id").trim());

		} catch (JSONException e) {
			e.printStackTrace();
		}

		return team;
	}

	public void insertPerformances(List<JSONHolder> holders) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		Set<Performance> allPerfs = new HashSet<Performance>();

		for (JSONHolder holder : holders) {
			JSONObject playerJSON = holder.getPlayer();
			JSONObject playerHistoryJSON = holder.getPlayerHistory();
			JSONObject playerStatsJSON = holder.getPlayerStats();

			Set<Performance> performances = populatePerformances(playerJSON, playerHistoryJSON, playerStatsJSON);
			allPerfs.addAll(performances);

		}
		for (Performance perf : allPerfs) {
			parameters.addValue("season", perf.getSeason());
			parameters.addValue("cornersWon", perf.getCornersWon());
			parameters.addValue("id", perf.getId());
			parameters.addValue("points", perf.getPoints());
			parameters.addValue("week", perf.getWeek());
			parameters.addValue("playerId", perf.getPlayerId());
			parameters.addValue("teamId", perf.getTeamId());
			parameters.addValue("assists", perf.getAssists());
			parameters.addValue("goalsAllowed", perf.getGoalsAllowed());
			parameters.addValue("foulsWon", perf.getFoulsWon());
			parameters.addValue("yellowCards", perf.getYellowCards());
			parameters.addValue("penaltyKickGoals", perf.getPenaltyKickGoals());
			parameters.addValue("foulsCommitted", perf.getFoulsCommitted());
			parameters.addValue("matchWinningGoal", perf.getMatchWinningGoal());
			parameters.addValue("blockedShots", perf.getBlockedShots());
			parameters.addValue("penaltyKickSaves", perf.getPenaltyKickSaves());
			parameters.addValue("tacklesWon", perf.getTacklesWon());
			parameters.addValue("give", perf.getGive());
			parameters.addValue("penaltiesCommitted", perf.getPenaltiesCommitted());
			parameters.addValue("shotsOnTarget", perf.getShotsOnTarget());
			parameters.addValue("redCard", perf.getRedCard());
			parameters.addValue("passessIntercepted", perf.getPassessIntercepted());
			parameters.addValue("penaltyKickMisses", perf.getPenaltyKickMisses());
			parameters.addValue("goals", perf.getGoals());
			parameters.addValue("gamesPlayed", perf.getGamesPlayed());
			parameters.addValue("defeat", perf.getDefeat());
			parameters.addValue("tie", perf.getTie());
			parameters.addValue("win", perf.getWin());
			parameters.addValue("cleanSheet", perf.getCleanSheet());
			parameters.addValue("setPieceGoals", perf.getSetPieceGoals());
			parameters.addValue("saves", perf.getSaves());
			parameters.addValue("crosses", perf.getCrosses());
			parameters.addValue("salary", perf.getSalaryReal());

			String sql = "insert into performance (id, player_id, season, points, week, assists, goals_allowed, fouls_won, yellow_cards, penalty_kick_goals,fouls_committed, match_winning_goal, blocked_shots, penalty_kick_saves, tackles_won, give, penalties_committed, shots_on_target, red_card, passes_intercepted, penalty_kick_misses, goals, games_played, defeat, tie, win, clean_sheet, own_goals, set_piece_goals, saves, successful_crosses, salary, owned  ) "
					+ "values (:id,:playerId,"
					+ Consts.CURRENT_YEAR
					+ ",:points, :week, :assists, :goalsAllowed,, :foulsWon,:yellowCards,:penaltyKickGoals, :foulsCommitted, :matchWinningGoal, :blockedShots, :penaltyKickSaves, :tacklesWon, :give, :penaltiesCommittes,:shotsOnTarget,:redCard, :passesIntercepted, :penaltyKickMisses, :goals, :gamesPlayed, :defeat, :tie, :win, :cleanSheet, :ownGoals, :setPieceGoals, :saves, :crosses, :salary, :owned )";
			namedParameterJdbcTemplate.update(sql, parameters);
		}

	}

	public void insertGames(List<JSONHolder> holders) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(select.getDataSource());
		Set<Game> allGames = new HashSet<Game>();

		for (JSONHolder holder : holders) {
			JSONObject playerJSON = holder.getPlayer();
			JSONObject playerHistoryJSON = holder.getPlayerHistory();
			JSONObject playerStatsJSON = holder.getPlayerStats();

			Set<Game> games = populateGames(playerJSON, playerHistoryJSON, playerStatsJSON);
			allGames.addAll(games);

		}
		for (Game g : allGames) {
			parameters.addValue("id", g.getId());
			parameters.addValue("away_team_id", g.getAwayTeamId());
			parameters.addValue("home_team_id", g.getHomeTeamId());
			parameters.addValue("away_team_score", g.getAwayScore());
			parameters.addValue("home_team_score", g.getHomeScore());
			String sql = "insert into game (id, away_team_id, home_team_id, away_team_score, home_team_score) values (:id,:away_team_id, :home_team_id, :away_team_score, :home_team_score)";
			namedParameterJdbcTemplate.update(sql, parameters);
		}

	}

	private Set<Game> populateGames(JSONObject playerJSON, JSONObject playerHistoryJSON, JSONObject playerStatsJSON) {
		Set<Game> gs = new HashSet<Game>();
		try {
			JSONArray weeks = playerHistoryJSON.getJSONArray("weeks");
			Integer length = weeks.length();

			for (Integer i = 0; i < length; i++) {

				JSONObject week = weeks.getJSONObject(i);
				JSONArray games = week.getJSONArray("games");
				gs.addAll(populateGames(games));
			}
		} catch (JSONException e) {
			e.printStackTrace();
		}

		return gs;

	}

	private Set<Game> populateGames(JSONArray games) {
		Integer length = games.length();
		Set<Game> gs = new HashSet<Game>();
		try {
			for (Integer i = 0; i < length; i++) {
				Game game = new Game();
				JSONObject jsGame = games.getJSONObject(i);
				if ("final".equalsIgnoreCase(jsGame.getString("game_status"))) {
					game.setId(jsGame.getString("id"));
					game.setAwayTeamId(jsGame.getString("away_team_id"));
					game.setHomeTeamId(jsGame.getString("home_team_id"));
					game.setHomeScore(jsGame.getJSONObject("score").getInt("home_score"));
					game.setAwayScore(jsGame.getJSONObject("score").getInt("away_score"));
					gs.add(game);
				}

			}
		} catch (JSONException e) {
			e.printStackTrace();
		}
		return gs;

	}

	private Set<Performance> populatePerformances(JSONObject playerJSON, JSONObject playerHistoryJSON, JSONObject playerStatsJSON) {

		Set<Performance> performances = new HashSet<Performance>();
		try {
			JSONArray weeks = playerHistoryJSON.getJSONArray("weeks");
			Integer length = weeks.length();

			for (Integer i = 0; i < length; i++) {
				Performance perf = new Performance();
				JSONObject week = weeks.getJSONObject(i);
				JSONArray games = week.getJSONArray("games");
				String season = week.getString("season");
				JSONObject stats = week.getJSONObject("stats");
				perf.setAssists(stats.getInt("A"));
				perf.setGoals(stats.getInt("G"));
				perf.setBlockedShots(stats.getInt("BS"));
				perf.setSetPieceGoals(stats.getInt("SPG"));
				perf.setCleanSheet(stats.getInt("CS"));
				perf.setRedCard(stats.getInt("RC"));
				perf.setGoalsAllowed(stats.getInt("GA"));
				perf.setDefeat(stats.getInt("L"));
				perf.setFoulsWon(stats.getInt("FW"));
				perf.setPenaltyKickGoals(stats.getInt("PKG"));
				perf.setPenaltyKickMisses(stats.getInt("PKM"));
				perf.setSaves(stats.getInt("SV"));
				perf.setTie(stats.getInt("T"));
				perf.setWin(stats.getInt("W"));
				perf.setOwnGoals(stats.getInt("OG"));
				perf.setPenaltyKickSaves(stats.getInt("PKS"));
				perf.setSalaryReal(stats.getDouble("salary"));
				perf.setCrosses(stats.getInt("SC"));
				perf.setGive(stats.getInt("GIVE"));
				perf.setMatchWinningGoal(stats.getInt("MWG"));
				perf.setFoulsCommitted(stats.getInt("FC"));
				perf.setYellowCards(stats.getInt("YC"));
				perf.setSeason(stats.getString("season"));
				perf.setPassessIntercepted(stats.getInt("PI"));
				perf.setGamesPlayed(stats.getInt("GP"));
				perf.setShotsOnTarget(stats.getInt("SOT"));
				perf.setTacklesWon(stats.getInt("TW"));
				perf.setWeek(stats.getInt("week"));
				perf.setPenaltiesCommitted(stats.getInt("PC"));
				perf.setOwned(stats.getDouble("owned"));
				Set<Game> gs = populateGames(games);
				perf.setGames(gs);
				performances.add(perf);

			}

		} catch (JSONException e) {
			e.printStackTrace();
		}

		return performances;
	}

}
